﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DAL;
using System.Data.SqlClient;
using VOL;

namespace BLL
{
    public class ThuQuyBL
    {
        private DataAccess dataAccess;

        public ThuQuyBL()
        {
            dataAccess = new DataAccess();
        }

        /// <summary>
        /// 1. Hiển thị danh sách thu theo tháng và năm đưa vào
        /// </summary>
        /// <returns></returns>
        public DataTable GetData(int thangthu, int namthu)
        {
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@THANGTHU", SqlDbType.Int) { Value = thangthu };
            param[1] = new SqlParameter("@NAMTHU", SqlDbType.Int) { Value = namthu };

            // cau query select
            string sql = "SELECT tblTHUQUY.IDTHU, tblNOIDUNGTHU.TENNDTHU, tblTHUQUY.SOTIENTHU, CONVERT(VARCHAR(10), tblTHUQUY.NTNTHU, 103) AS NTNTHU , tblTHUQUY.GHICHU " +
                         " FROM tblNOIDUNGTHU INNER JOIN tblTHUQUY ON tblNOIDUNGTHU.IDNDTHU = tblTHUQUY.IDNOIDUNG " + 
                         " WHERE (tblNOIDUNGTHU.IDNDTHU <> 1) AND tblTHUQUY.THANGTHU = @THANGTHU AND tblTHUQUY.NAMTHU = @NAMTHU ";
            DataTable dt = dataAccess.GetData(sql, param);

            return dt;
        }

        /// <summary>
        /// 1. Hiển thị danh sách thu CÁN BỘ theo tháng và năm đưa vào 
        /// </summary>
        /// <returns></returns>
        public DataTable GetDataCB(int idcanbo, int namthu)
        {
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@IDCANBO", SqlDbType.Int) { Value = idcanbo };
            param[1] = new SqlParameter("@NAMTHU", SqlDbType.Int) { Value = namthu };
         

            // cau query select
            string sql = "SELECT tblTHUQUY.IDTHU, tblTHUQUY.THANGTHU, tblTHUQUY.SOTIENTHU, CONVERT(VARCHAR(10), tblTHUQUY.NTNTHU, 103) AS NTNTHU , tblTHUQUY.GHICHU " +
                         " FROM tblTHUQUY " +
                         " WHERE (tblTHUQUY.IDNOIDUNG = 1) AND tblTHUQUY.IDCANBO = @IDCANBO AND tblTHUQUY.NAMTHU = @NAMTHU ORDER BY THANGTHU DESC";
            DataTable dt = dataAccess.GetData(sql, param);

            return dt;
        }


        /// <summary>
        /// 1. Hiển thị danh sách thu theo cán bộ
        /// </summary>
        /// <returns></returns>
        public DataTable GetDataThuCB(int namthu)
        {
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@NAMTHU", SqlDbType.Int) { Value = namthu };

            // cau query select
            string sql = "SELECT tblTHUQUY.IDTHU, tblDMCANBO.HOTEN, tblTHUQUY.SOTIENTHU, tblTHUQUY.NTNTHU, tblTHUQUY.THANGTHU FROM tblNOIDUNGTHU INNER JOIN " +
                         " tblTHUQUY ON tblNOIDUNGTHU.IDNDTHU = tblTHUQUY.IDNOIDUNG INNER JOIN tblDMCANBO ON tblTHUQUY.IDCANBO = tblDMCANBO.IDCANBO " +
                         " WHERE (tblNOIDUNGTHU.IDNDTHU = 1) AND tblTHUQUY.NAMTHU = @NAMTHU ORDER BY tblDMCANBO.IDCANBO DESC ";
                        
            DataTable dt = dataAccess.GetData(sql, param);

            return dt;
        }

        /// <summary>
        /// 2. Thêm THU
        /// </summary>
        /// <param name="obj">THUQUY</param>
        /// <returns>true</returns>
        public bool InsertData(THUQUY obj)
        {
            SqlParameter[] param = new SqlParameter[8];
            param[0] = new SqlParameter("@IDNoiDung", SqlDbType.Int) { Value = obj.IDNoiDung };
            param[1] = new SqlParameter("@SoTienThu", SqlDbType.Float) { Value = obj.SoTienThu };
            param[2] = new SqlParameter("@IDCanBo", SqlDbType.Int) { Value = obj.IDCanBo };
            param[3] = new SqlParameter("@NgayThu", SqlDbType.Int) { Value = obj.NgayThu };
            param[4] = new SqlParameter("@ThangThu", SqlDbType.Int) { Value = obj.ThangThu };
            param[5] = new SqlParameter("@NamThu", SqlDbType.Int) { Value = obj.NamThu };
            param[6] = new SqlParameter("@NTNThu", SqlDbType.DateTime) { Value = obj.NTNThu };
            param[7] = new SqlParameter("@GhiChu", SqlDbType.NVarChar) { Value = obj.GhiChu };

            // cau query select
            string sql = "INSERT INTO tblTHUQUY (IDNOIDUNG, SOTIENTHU, IDCANBO, NGAYTHU, THANGTHU, NAMTHU, NTNTHU, GHICHU) VALUES (@IDNoiDung, @SoTienThu, @IDCanBo, @NgayThu, @ThangThu, @NamThu, @NTNThu,@GhiChu )";
            dataAccess.Execute(sql, param);

            return true;
        }

        /// <summary>
        /// 3. Xóa THU
        /// </summary>
        /// <param name="MaNguoiDung">IDTHU INT</param>
        /// <returns>true</returns>
        public bool DeleteData(int IDTHU)
        {
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@IDTHU", SqlDbType.Int) { Value = IDTHU };

            //LAY SO  TIEN CHI TRUOC DO
            float sumsotienthu;
            int namthu,idNoidungthu;

            string sqlquy = "SELECT  NAMTHU,IDNOIDUNG FROM tblTHUQUY " +
                         " WHERE  IDTHU = @IDTHU ";
            DataTable dt = dataAccess.GetData(sqlquy, param);

            namthu = int.Parse(dt.Rows[0]["NAMTHU"].ToString());
            idNoidungthu = int.Parse(dt.Rows[0]["IDNOIDUNG"].ToString());

            
            // cau query select
            param[0] = new SqlParameter("@IDTHU", SqlDbType.Int) { Value = IDTHU };
            string sql = "DELETE FROM tblTHUQUY WHERE IDTHU = @IDTHU";
            dataAccess.Execute(sql, param);

            //lay lai so  tien thu sau khi xoa
            sumsotienthu = GetSumSotienThu(namthu, idNoidungthu);

            //cap nhat lại quy phong
            UpdateQuyPhong(namthu,sumsotienthu,idNoidungthu);

            return true;
        }

        /// <summary>
        /// 4. sửa THU
        /// </summary>
        /// <param name="obj">THUQUY</param>
        /// <returns>true</returns>
        public bool UpdateData(THUQUY obj)
        {
            SqlParameter[] param = new SqlParameter[9];
            param[0] = new SqlParameter("@IDNoiDung", SqlDbType.Int) { Value = obj.IDNoiDung };
            param[1] = new SqlParameter("@SoTienThu", SqlDbType.Float) { Value = obj.SoTienThu };
            param[2] = new SqlParameter("@IDCanBo", SqlDbType.Int) { Value = obj.IDCanBo };
            param[3] = new SqlParameter("@NgayThu", SqlDbType.Int) { Value = obj.NgayThu };
            param[4] = new SqlParameter("@ThangThu", SqlDbType.Int) { Value = obj.ThangThu };
            param[5] = new SqlParameter("@NamThu", SqlDbType.Int) { Value = obj.NamThu };
            param[6] = new SqlParameter("@NTNThu", SqlDbType.DateTime) { Value = obj.NTNThu };
            param[7] = new SqlParameter("@GhiChu", SqlDbType.NVarChar) { Value = obj.GhiChu };

            param[8] = new SqlParameter("@IDTHU", SqlDbType.Int) { Value = obj.IDThu };
            // cau query select
            string sql = "UPDATE tblTHUQUY SET IDNOIDUNG = @IDNoiDung, SOTIENTHU = @SoTienThu, IDCANBO = @IDCanBo, NGAYTHU = @NgayThu, THANGTHU = @ThangThu, NAMTHU = @NamThu, NTNTHU = @NTNThu, GHICHU = @GhiChu " +
                           " WHERE IDTHU = @IDTHU";
            dataAccess.Execute(sql, param);

            return true;
        }

        /// <summary>
        /// 5. Tìm theo Mã THU
        /// </summary>
        /// <param name="IDTHU"></param>
        /// <returns></returns>
        public DataTable GetDataById(int IDTHU)
        {
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@IDTHU", SqlDbType.Int) { Value = IDTHU };
            // cau query select
            string sql = "SELECT * FROM tblTHUQUY WHERE IDTHU = @IDTHU";
            DataTable dt = dataAccess.GetData(sql, param);

            return dt;
        }

        /// <summary>
        /// 5.1. Tìm theo Mã THU
        /// </summary>
        /// <param name="THUQUY"></param>
        /// <returns></returns>
        //Tìm
        public DataTable searchIDTHU(THUQUY obj)
        {
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@IDTHU", SqlDbType.Int) { Value = obj.IDThu };

            string strSql = "SELECT * FROM tblTHUQUY WHERE IDTHU = @IDTHU";
            return dataAccess.GetData(strSql, param);
        }

        //6. kiểm tra có dữ liệu thu quỹ phòng của năm đưa vào
        public bool kiemtraQuyNam(int nam)
        {
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@NAM", SqlDbType.Int) { Value = nam };
            
            // cau query select
            string sql = "SELECT * FROM tblQUYPHONG WHERE NAM = @NAM ";
            DataTable dt = dataAccess.GetData(sql, param);
            if (dt.Rows.Count > 0)
                return true;
            else return false;
        }

        ///7.CẬP NHẬT LẠI BẢNG QUY PHONG
        ///
        /// 

        public void UpdateQuyPhong(int nam, float sotienthu, int noidungthu)
        {
            //cap nhật lại số tiền thu
            QuyPhongBL _quyPhongBL = new QuyPhongBL();
            QUYPHONG _objquyphong = new QUYPHONG();

            DataTable dtquyphong = _quyPhongBL.GetData(nam);

            _objquyphong.Nam = nam;
           
            _objquyphong.SoTienNamTruoc = float.Parse(dtquyphong.Rows[0]["SOTIENNAMTRUOC"].ToString());
            if (noidungthu == 1){
                _objquyphong.SoTienThuKhac = float.Parse(dtquyphong.Rows[0]["SOTIENTHUKHAC"].ToString());
                _objquyphong.SoTienThuCB = sotienthu;
                _objquyphong.SoTienConLai = float.Parse(dtquyphong.Rows[0]["SOTIENNAMTRUOC"].ToString()) + float.Parse(dtquyphong.Rows[0]["SOTIENTHUKHAC"].ToString()) + sotienthu - float.Parse(dtquyphong.Rows[0]["SOTIENCHI"].ToString());
            }
            else{
                _objquyphong.SoTienThuKhac = sotienthu;
                _objquyphong.SoTienThuCB = float.Parse(dtquyphong.Rows[0]["SOTIENTHUCB"].ToString());
                _objquyphong.SoTienConLai = float.Parse(dtquyphong.Rows[0]["SOTIENNAMTRUOC"].ToString()) + float.Parse(dtquyphong.Rows[0]["SOTIENTHUCB"].ToString()) + sotienthu - float.Parse(dtquyphong.Rows[0]["SOTIENCHI"].ToString());
            }

            _objquyphong.SoTienChi = float.Parse(dtquyphong.Rows[0]["SOTIENCHI"].ToString());

            _quyPhongBL.UpdateData(_objquyphong);
        }

        //8. lay so tien thu tong
        public float GetSumSotienThu(int namthu, int noidungthu)
        {
           // float SumSotienthu;
            string sql;
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@IDNOIDUNGTHU", SqlDbType.Int) { Value = noidungthu };
            param[1] = new SqlParameter("@NAMTHU", SqlDbType.Int) { Value = namthu };
            
            // cau query select
            if (noidungthu == 1)
            {
               sql = "SELECT IDNOIDUNG, SUM(SOTIENTHU) AS SOTIENTHU, NAMTHU FROM tblTHUQUY WHERE (NAMTHU = @NAMTHU) AND (IDNOIDUNG = 1) GROUP BY IDNOIDUNG, NAMTHU ";
            }
            else 
               sql = "SELECT SUM(SOTIENTHU) AS SOTIENTHU, NAMTHU FROM tblTHUQUY WHERE (NAMTHU = @NAMTHU) AND (IDNOIDUNG <> 1) GROUP BY NAMTHU ";
            
            DataTable dt = dataAccess.GetData(sql, param);

            if (dt.Rows.Count>0)
            {
                return float.Parse(dt.Rows[0]["SOTIENTHU"].ToString());
            }
            else
                return 0;
        }
        //8. lấy số tiền còn  lại của năm trước
        public float GetSoTienNamTruoc(int nam)
        {
            float SoTienNamTruoc;
            //cap nhật lại số tiền thu
            QuyPhongBL _quyPhongBL = new QuyPhongBL();

            DataTable dtquyphong = _quyPhongBL.GetData(nam);

            if (dtquyphong.Rows.Count > 0)
            {
                SoTienNamTruoc = float.Parse(dtquyphong.Rows[0]["SOTIENCONLAI"].ToString());
            }
            else SoTienNamTruoc = 0;
           
            return SoTienNamTruoc;
        }


        //9.  kiem tra can bo da duoc thu thang do nam do chua
        public bool checkCBThuThang(int idcanbo, int thangthu, int namthu)
        {
            SqlParameter[] param = new SqlParameter[3];
            param[0] = new SqlParameter("@IDCANBO", SqlDbType.Int) { Value = idcanbo };
            param[1] = new SqlParameter("@THANGTHU", SqlDbType.Int) { Value = thangthu };
            param[2] = new SqlParameter("@NAMTHU", SqlDbType.Int) { Value = namthu };

            // cau query select
            string sql = "SELECT * FROM tblTHUQUY WHERE (IDCANBO = @IDCANBO) AND (THANGTHU = @THANGTHU) AND (NAMTHU = @NAMTHU) ";
            DataTable dt = dataAccess.GetData(sql, param);
            if (dt.Rows.Count > 0)
                return true;
            else return false;
        }

        //10.  kiem tra can bo da duoc thu du 12 thang cua nam chua
        public bool checkCBThuNam(int idcanbo, int namthu)
        {
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@IDCANBO", SqlDbType.Int) { Value = idcanbo };
            param[1] = new SqlParameter("@NAMTHU", SqlDbType.Int) { Value = namthu };

            // cau query select
            string sql = "SELECT * FROM tblTHUQUY WHERE (IDCANBO = @IDCANBO) AND (NAMTHU = @NAMTHU) ";
            DataTable dt = dataAccess.GetData(sql, param);
            if (dt.Rows.Count == 12)
                return true;
            else return false;
        }
    }
}
